package unipi.database;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Tasks
{
	public static ErrorCodes assignNew(String title, String desc, String group, String hours, String startDate, String daysDue, String assignedBy, String[] assignedTo)
	{
		String grp = group == null ? "null" : "'" + group + "'";
		
		String columns = "title, description, `group`, hoursRequired, assignedDate, startDate, dueDate, status, assignedBy";
		String values = "'" + title + "','" + desc + "',"+ grp + "," +  hours + "," +
						"curdate(), '" + startDate + "','" + daysDue + "' , 'pending', " + assignedBy;
		
		
		int taskID = SQLMethods.Insert("Tasks", columns, values);
		if(taskID == -1) return ErrorCodes.databaseError;
		
		return assign(Integer.toString(taskID), assignedTo);
	}
	
	public static ErrorCodes assign(String taskID, String[] assignedTo)
	{
		String[] values = new String[assignedTo.length];
		
		for(int x = 0; x < assignedTo.length; x++)
		{
			values[x] = taskID + " , " + assignedTo[x];			
		}		
		
		return SQLMethods.Insert("TaskAssignments", "taskID, userID", values)  != -1?ErrorCodes.OK: ErrorCodes.databaseError;		
	}
	
	public static boolean isAssigned(String uid, String taskid)
	{
		int ncols = 1;
		String query = "select userID from TaskAssignments\r\n" + 
						"where userID = " + uid + " and TaskID = " + taskid;
		
		List<List<String>> L = SQLMethods.Select(query, ncols);
		
		if(L == null) return false;
		if(L.size() == 0) return false;
		
		return true;		
	}
	
	public static ErrorCodes addProgress(String title, String description, String hoursWorked, String dateWorked, String taskID, String userID, boolean done)
	{
		String columns = "title, description, hoursWorked, dateWorked, dateAdded, taskID, userID";
		String values = "'" + title + "', '" + description + "'," + hoursWorked + ",'" + dateWorked + "', curdate() ," + taskID + "," + userID;
		
		int updateStartedDate = SQLMethods.Update("Tasks", "startedDate", "'" +  dateWorked + "'", "startedDate is null and id = " + taskID);
		if(updateStartedDate == -1) return ErrorCodes.databaseError;
		
		if(done)
		{			
			int updateEndDate = SQLMethods.Update("Tasks", "endDate", "'" +  dateWorked + "'", "id = " + taskID);
			if(updateEndDate == -1) return ErrorCodes.otherError;
		}			
		
		return SQLMethods.Insert("TaskProgress", columns, values) == -1? ErrorCodes.databaseError : ErrorCodes.OK;
	}
	
	public static List<Map<String, String>> getProgress(String userID, String taskID, int page, int resultsPerPage)
	{
		int ncols = 8;
		String query = "select p.id, t.title, p.title, p.description, \r\n" + 
						"p.hoursWorked, p.dateWorked, p.dateAdded, p.uname from Tasks t\r\n" + 
						"inner join\r\n" + 
						"(\r\n" + 
						"    select t2.*, concat(u.surname, ' ' , u.name) uname from Users u\r\n" + 
						"    inner join\r\n" + 
						"    (    \r\n" + 
						"        select tp.* from TaskProgress tp\r\n" + 
						"        inner join TaskAssignments ta\r\n" + 
						"        on tp.taskID = ta.taskID\r\n" + 
						"        where ta.userID = " + userID + "\r\n" + 
						"    ) t2\r\n" + 
						"    on u.id = t2.userID \r\n" + 
						") p\r\n" + 
						"on t.id = p.taskID\r\n";
		
		if(taskID != null) query += "where taskID = " + taskID;
		
		query += " order by dateAdded desc\r\n" +
				  " limit " + (resultsPerPage*page) + "," + resultsPerPage;
				
		List<List<String>> result = SQLMethods.Select(query, ncols);
		
		if(result == null) return null;
		
		List<Map<String, String>> progressList = new ArrayList<Map<String, String>>();
		
		for(List<String> row : result)
		{
			Map<String, String> map = new HashMap<String, String>();
			map.put("id", row.get(0));
			map.put("task", row.get(1));
			map.put("title", row.get(2));
			map.put("desc", row.get(3));
			map.put("hours", row.get(4));
			map.put("dateworked", row.get(5));
			map.put("dateadded", row.get(6));
			map.put("user", row.get(7));
			progressList.add(map);
		}
		
		return progressList;
	}
	
	public static List<List<String>> getProgressedTasks(String userID)
	{
		int ncols = 2;
		String query = "select distinct t.id, t.title from taskProgress tp\r\n" + 
						"inner join Tasks t\r\n" + 
						"on t.id = tp.taskID\r\n" + 
						"where tp.userID = " + userID + "\r\n";
		
		return SQLMethods.Select(query, ncols);		
	}
	
	public static List<Map<String, String>> getProgress(String userID, int page, int resultsPerPage)
	{
		return getProgress(userID, null, page, resultsPerPage);		
	}
	
	public static List<List<String>> getCalendarTasks(String userID, String month, String year)
	{
		int ncols = 2;
		String query = 	"select group_concat(\r\n" +
						"    concat('<a href=\"TaskDetails?id=', t.id,\r\n" + 
						"    '\" class=\"genericLink ',\r\n" + 
						"        if(t.dueDate < now(),'calLate',\r\n" + 
						"        (if(t.startDate < now(),'calShouldStarted',\r\n" + 
						"        (if(t.startedDate is null,'calNotStarted','calOK'))))),\r\n" + 
						"    '\">', title, '</a>')\r\n" + 
						"),\r\n" + 
						"day(dueDate) as day\r\n" +
						"from Tasks t\r\n" + 
						"inner join TaskAssignments ta\r\n" + 
						"on t.id = ta.taskID\r\n" + 
						"where month(dueDate) = " + month + "  and year(dueDate) = " + year + "\r\n" +
						"and ta.userID = " + userID + " and endDate is null \r\n" + 
						"group by day";
				
		return SQLMethods.Select(query, ncols);		
	}
	
	public static List<List<String>> getCalendarProgress(String userID, String month, String year)
	{
		int ncols = 2;
		String query = "select group_concat('<a class=\"genericLink\" href=\"Blog?taskid=', t.id,'&uid='," + userID + ", '\">',\r\n" + 
						"t.title, ': ', tp.hoursWorked, if(tp.hoursWorked = 1, ' ώρα',' ώρες'), '</a>'\r\n" + 
						"),\r\n" + 
						"day(dateAdded)\r\n" + 
						"from TaskProgress tp\r\n" + 
						"inner join Tasks t\r\n" + 
						"on t.id = tp.taskID\r\n" +
						"where userID = " + userID + " and \r\n" + 
						"month(dateAdded) = " + month + " and year(dateAdded) = " + year + "\r\n" + 
						"group by day(dateAdded)";
		
		return SQLMethods.Select(query, ncols);		
	}
	
	public static ErrorCodes endTask(String taskID)
	{		
		return SQLMethods.Update("Tasks", "endDate", "curdate()", "where id = " + taskID) != -1?ErrorCodes.OK: ErrorCodes.databaseError;
	}
	
	public static Map<String, String> get(String taskID, boolean usebr)
	{
		int ncols = 12;
		String query = "select t.title, t.description, coalesce(t.`group`, 'Δεν έχει οριστεί ομάδα.'), t.hoursRequired," +
						"concat(format(t.percentCompleted*100, 2), '%'), t.endDate,\r\n" + 
						"t.assignedDate, t.startDate, t.dueDate, coalesce(t.startedDate, 'Δεν έχει ξεκινήσει ακόμα.')," +
						"concat(u.surname, ' ', u.name), t.assignedBy\r\n" + 
						"from Tasks t\r\n" + 
						"inner join Users u\r\n" + 
						"on t.assignedBy = u.id\r\n" + 
						"where t.id = " + taskID;
		
		List<List<String>> task = SQLMethods.Select(query, ncols);
		
		if(task == null) return null;
		if(task.size() == 0) return null;
		
		if(usebr) task.get(0).set(1, task.get(0).get(1).replaceAll("(\r\n|\n)", "<br />"));
		
		Map<String, String> taskMap = new HashMap<String, String>();
		
		taskMap.put("title", 			task.get(0).get(0));
		taskMap.put("description", 		task.get(0).get(1));
		taskMap.put("group", 			task.get(0).get(2));
		taskMap.put("hoursRequired", 	task.get(0).get(3));
		taskMap.put("percentCompleted", task.get(0).get(4));
		taskMap.put("endDate", 			task.get(0).get(5));
		taskMap.put("assignedDate", 	task.get(0).get(6));
		taskMap.put("startDate", 		task.get(0).get(7));
		taskMap.put("dueDate", 			task.get(0).get(8));
		taskMap.put("startedDate", 		task.get(0).get(9));
		taskMap.put("assignedBy", 		task.get(0).get(10));
		taskMap.put("assignerID", 		task.get(0).get(11));
		
		return taskMap;		
	}
	
	public static Map<String, String> get(String taskID)
	{
		return get(taskID, true);
	}
	
	public static String getTaskUsers(String taskID)
	{
		int ncols = 1;
		String query = "select \r\n" + 
						"group_concat('<a class=\"genericLink\" href=\"Profile?id=', u.id, '\"> '," +
						" u.surname, ' ', u.name, '</a>')\r\n " +
						"from TaskAssignments ta\r\n" + 
						"inner join Users u\r\n" + 
						"on ta.userID = u.id\r\n" + 
						"where taskID = " + taskID + "\r\n" + 
						"group by taskID";
		
		List<List<String>> res = SQLMethods.Select(query, ncols);
		if(res == null) return null;
		
		return res.get(0).get(0);		
	}
	
	public static List<List<String>> getPending(String userID)
	{
		int ncols = 9;
		String query = "select \r\n " +
						"concat('<a class=\"genericLink\" href=\"TaskDetails?id=', t1.id ,'\">', t1.title, '</a>'), \r\n" +
						"coalesce(t1.`group`, 'Χωρίς ομάδα'), t1.hoursRequired, concat(format(t1.percentCompleted*100, 2), '%'),\r\n" + 
						"t1.startDate, t1.dueDate, coalesce(t1.startedDate, '-'),\r\n" + 
						"concat('<a class=\"genericLink\" href=\"Profile?id=', u.id, '\">',  u.surname,' ' ,u.name, '</a>'), \r\n" +
						"concat('<a class=\"genericLink\" href=\"TaskProgress?id=', t1.id, '\">Προσθήκη</a>') from \r\n" + 
						"( \r\n" + 
						"    select t.id, t.title, t.description, t.`group`, t.hoursRequired, t.percentCompleted, \r\n" + 
						"    t.startDate, t.dueDate, t.startedDate, t.assignedBy from Tasks t \r\n" + 
						"    inner join \r\n" + 
						"    TaskAssignments ta \r\n" + 
						"    on t.id = ta.taskID \r\n" + 
						"    where ta.userID = " + userID + " and t.endDate is null\r\n" + 
						") t1\r\n" + 
						"inner join Users u \r\n" + 
						"on u.id = t1.assignedBy order by t1.`group`;";
		
		List<List<String>> taskList = SQLMethods.Select(query, ncols);		
		
		if(taskList == null) return null;
				
		List<String> coltitles = new ArrayList<String>();
		coltitles.add("Τίτλος");
		coltitles.add("Ομάδα");
		coltitles.add("Ώρες");
		coltitles.add("Πρόοδος");
		coltitles.add("Έναρξη στις");
		coltitles.add("Προθεσμία μέχρι");
		coltitles.add("Άρχισε στις");
		coltitles.add("Ανατέθηκε από");	
		coltitles.add("");		
		taskList.add(coltitles);
		
		return taskList;
	}	
	
	public static List<List<String>> getCompleted(String userID)
	{
		int ncols = 7;		
		String query = "select \r\n " +
						"concat('<a class=\"genericLink\" href=\"TaskDetails?id=', t1.id ,'\">', t1.title, '</a>'), \r\n" +
						"coalesce(t1.`group`, 'Χωρίς ομάδα'), t1.hours, concat(format(t1.percentCompleted*100, 2), '%'), \r\n" + 
						"coalesce(t1.startedDate, '-'), t1.endDate,\r\n" + 
						"concat('<a class=\"genericLink\" href=\"Profile?id=', u.id, '\">',  u.surname,' ' ,u.name, '</a>') from  " +
						"( \r\n" + 
						"    select t.id, t.title, t.`group`, tp.hours, t.percentCompleted,  \r\n" + 
						"    t.startedDate, t.endDate, t.assignedBy from Tasks t\r\n" + 
						"    inner join\r\n" + 
						"    TaskAssignments ta \r\n" + 
						"    on t.id = ta.taskID \r\n" + 
						"    inner join\r\n" + 
						"    (\r\n" + 
						"        select sum(hoursWorked) hours, taskID\r\n" + 
						"        from taskProgress\r\n" + 
						"        group by taskID\r\n" + 
						"    ) tp\r\n" + 
						"    on tp.taskID = t.id\r\n" + 
						"    where ta.userID = " + userID + " and endDate is not null\r\n" + 
						") t1\r\n" + 
						"inner join Users u \r\n" + 
						"on u.id = t1.assignedBy " +
						"order by t1.`group`;";
		
		List<List<String>> taskList = SQLMethods.Select(query, ncols);		
		
		if(taskList == null) return null;
	
		List<String> coltitles = new ArrayList<String>();
		coltitles.add("Τίτλος");
		coltitles.add("Ομάδα");
		coltitles.add("Ώρες Εργασίας");
		coltitles.add("Πρόοδος");
		coltitles.add("Άρχισε στις");
		coltitles.add("Ολοκληρώθηκε στις");
		coltitles.add("Ανατέθηκε από");	
		taskList.add(coltitles);		
		
		return taskList;
	}
	
	public static List<List<String>> getNotStarted(String userID)
	{
		int ncols = 8;
		String query = "select \r\n " +
						"concat('<a class=\"genericLink\" href=\"TaskDetails?id=', t1.id ,'\">', t1.title, '</a>'), \r\n" +
						"coalesce(t1.`group`, 'Χωρίς ομάδα'), t1.hoursRequired,  concat(format(t1.percentCompleted*100, 2), '%'),\r\n" + 
						"t1.startDate, t1.dueDate, \r\n" + 
						"concat('<a class=\"genericLink\" href=\"Profile?id=', u.id, '\">',  u.surname,' ' ,u.name, '</a>'),\r\n" + 
						"concat('<a class=\"genericLink\" href=\"TaskProgress?id=', t1.id, '\">Προσθήκη</a>') from " + 
						"( \r\n" + 
						"    select t.title, t.`group`, t.hoursRequired, t.percentCompleted, \r\n" + 
						"    t.startDate, t.dueDate, t.assignedBy, t.id from Tasks t \r\n" + 
						"    inner join \r\n" + 
						"    TaskAssignments ta \r\n" + 
						"    on t.id = ta.taskID \r\n" + 
						"    where ta.userID = " + userID + " and t.startedDate is null\r\n" + 
						") t1\r\n" + 
						"inner join Users u \r\n" + 
						"on u.id = t1.assignedBy " +
						"order by t1.`group`;";
		
		List<List<String>> taskList = SQLMethods.Select(query, ncols);		
		
		if(taskList == null) return null;
		
		List<String> coltitles = new ArrayList<String>();
		coltitles.add("Τίτλος");
		coltitles.add("Ομάδα");
		coltitles.add("Ώρες");
		coltitles.add("Πρόοδος");
		coltitles.add("Έναρξη στις");
		coltitles.add("Προθεσμία μέχρι");
		coltitles.add("Ανατέθηκε από");	
		coltitles.add("");
		taskList.add(coltitles);	
		
		return taskList;	
	}
	
	public static List<List<String>> getComingDue(String userID)
	{
		int ncols = 10;
		String query = "select \r\n " +
						"concat('<a class=\"genericLink\" href=\"TaskDetails?id=', t1.id ,'\">', t1.title, '</a>'), \r\n" +
						"coalesce(t1.`group`, 'Χωρίς ομάδα'), t1.hoursRequired, concat(format(t1.percentCompleted*100, 2), '%'),\r\n" + 
						"t1.startDate, t1.dueDate, coalesce(t1.startedDate, '-'), t1.daysToGo, \r\n" + 
						"concat('<a class=\"genericLink\" href=\"Profile?id=', u.id, '\">',  u.surname,' ' ,u.name, '</a>'),\r\n" + 
						"concat('<a class=\"genericLink\" href=\"TaskProgress?id=', t1.id, '\">Προσθήκη</a>') from " + 
						"( \r\n" + 
						"    select t.title, t.`group`, t.hoursRequired, t.percentCompleted, \r\n" + 
						"    t.startDate, t.dueDate, t.startedDate, t.assignedBy,\r\n" + 
						"    datediff(t.dueDate, curdate()) daysToGo, datediff(t.dueDate, t.assignedDate) totalDays, t.id from Tasks t \r\n" + 
						"    inner join \r\n" + 
						"    TaskAssignments ta \r\n" + 
						"    on t.id = ta.taskID \r\n" + 
						"    where ta.userID = " + userID + " and t.endDate is null \r\n" + 
						") t1\r\n" + 
						"inner join Users u \r\n" + 
						"on u.id = t1.assignedBy\r\n" + 
						"where t1.totalDays*0.15 > t1.daysToGo and t1.daysToGo > 0 or t1.daysToGo = 1 " +
						"order by t1.`group`;";
		
		List<List<String>> taskList = SQLMethods.Select(query, ncols);		
		
		if(taskList == null) return null;
		
		List<String> coltitles = new ArrayList<String>();
		coltitles.add("Τίτλος");
		coltitles.add("Ομάδα");
		coltitles.add("Ώρες");
		coltitles.add("Πρόοδος");
		coltitles.add("Έναρξη στις");
		coltitles.add("Προθεσμία μέχρι");
		coltitles.add("Άρχισε στις");
		coltitles.add("Ημέρες που απομένουν");
		coltitles.add("Ανατέθηκε από");
		coltitles.add("");
		taskList.add(coltitles);	
		
		return taskList;
	}
	
	public static List<List<String>> getOverDue(String userID)
	{
		int ncols = 10;
		String query = "select \r\n " +
						"concat('<a class=\"genericLink\" href=\"TaskDetails?id=', t1.id ,'\">', t1.title, '</a>'), \r\n" +
						"coalesce(t1.`group`, 'Χωρίς ομάδα'), t1.hoursRequired, concat(format(t1.percentCompleted*100, 2), '%'),\r\n" + 
						"t1.startDate, t1.dueDate, coalesce(t1.startedDate, '-'), -t1.daysToGo,\r\n" + 
						"concat('<a class=\"genericLink\" href=\"Profile?id=', u.id, '\">',  u.surname,' ' ,u.name, '</a>'),\r\n" + 
						"concat('<a class=\"genericLink\" href=\"TaskProgress?id=', t1.id, '\">Προσθήκη</a>') from " +
						"( \r\n" + 
						"    select t.title, t.`group`, t.hoursRequired, t.percentCompleted, \r\n" + 
						"    t.startDate, t.dueDate, t.startedDate, t.assignedBy,\r\n" + 
						"    datediff(t.dueDate, curdate()) daysToGo, t.id from Tasks t \r\n" + 
						"    inner join \r\n" + 
						"    TaskAssignments ta \r\n" + 
						"    on t.id = ta.taskID \r\n" + 
						"    where ta.userID = " + userID + " and t.endDate is null \r\n" + 
						") t1\r\n" + 
						"inner join Users u \r\n" + 
						"on u.id = t1.assignedBy\r\n" + 
						"where daysToGo < 0 " +
						"order by t1.`group`;";
				
		List<List<String>> taskList = SQLMethods.Select(query, ncols);		
		
		if(taskList == null) return null;
			
		List<String> coltitles = new ArrayList<String>();
		coltitles.add("Τίτλος");
		coltitles.add("Ομάδα");
		coltitles.add("Ώρες");
		coltitles.add("Πρόοδος");
		coltitles.add("Έναρξη στις");
		coltitles.add("Προθεσμία μέχρι");
		coltitles.add("Άρχισε στις");
		coltitles.add("Ημέρες υπέρβασης");
		coltitles.add("Ανατέθηκε από");	
		coltitles.add("");
		taskList.add(coltitles);	
		
		return taskList;
	}
	
	public static List<List<String>> getShouldHaveStarted(String userID)
	{
		int ncols = 8;
		String query = "select \r\n " +
						"concat('<a class=\"genericLink\" href=\"TaskDetails?id=', t1.id ,'\">', t1.title, '</a>'), \r\n" +
						"coalesce(t1.`group`, 'Χωρίς ομάδα'), t1.hoursRequired,\r\n" + 
						"concat(format(t1.percentCompleted*100, 2), '%'),\r\n" + 
						"t1.startDate, t1.dueDate,\r\n" + 
						"concat('<a class=\"genericLink\" href=\"Profile?id=', u.id, '\">',  u.surname,' ' ,u.name, '</a>'),\r\n" + 
						"concat('<a class=\"genericLink\" href=\"TaskProgress?id=', t1.id, '\">Προσθήκη</a>') from\r\n" + 
						"(\r\n" + 
						"    select t.title, t.`group`, t.hoursRequired, t.percentCompleted,\r\n" + 
						"    t.startDate, t.dueDate, t.assignedBy, t.id\r\n" + 
						"    from Tasks t\r\n" + 
						"    inner join TaskAssignments ta\r\n" + 
						"    on t.id = ta.taskID\r\n" + 
						"    where t.startDate < now() and t.startedDate is null and ta.userID = 94\r\n" + 
						") t1\r\n" + 
						"inner join Users u\r\n" + 
						"on u.id = t1.assignedBy\r\n" + 
						"order by t1.`group`";
		
		List<List<String>> taskList = SQLMethods.Select(query, ncols);		
		
		if(taskList == null) return null;
			
		List<String> coltitles = new ArrayList<String>();
		coltitles.add("Τίτλος");
		coltitles.add("Ομάδα");
		coltitles.add("Ώρες");
		coltitles.add("Πρόοδος");
		coltitles.add("Έναρξη στις");
		coltitles.add("Προθεσμία μέχρι");
		coltitles.add("Ανατέθηκε από");	
		coltitles.add("");
		taskList.add(coltitles);	
		
		return taskList;
		
	}
	
	public static List<List<String>> getAssigned(String userID)
	{
		int ncols = 8;
		String query = "select \r\n " +
						"concat('<a class=\"genericLink\" href=\"TaskDetails?id=', id ,'\">', title, '</a>'), \r\n" +
						"coalesce(`group`, 'Χωρίς ομάδα'), concat(format(percentCompleted*100, 2), '%'), \r\n" + 
						"assignedDate, coalesce(startDate, '-'), coalesce(endDate, '-'), dueDate,\r\n" +
						"concat('<a class=\"genericLink\" href=\"EditTask?id=', id, '\">Επεξεργασία</a>')\r\n" +
						"from Tasks\r\n" + 
						"where assignedBy = " + userID + "\r\n" +
						"order by `group`";
		
		List<List<String>> taskList = SQLMethods.Select(query, ncols);
		
		if(taskList == null) return null;
		
		List<String> coltitles = new ArrayList<String>();
		coltitles.add("Τίτλος");
		coltitles.add("Ομάδα");
		coltitles.add("Πρόοδος");
		coltitles.add("Ανάθεση στις");
		coltitles.add("Άρχισε στις");
		coltitles.add("Ολοκληρώθηκε στις");
		coltitles.add("Προθεσμία μέχρι");
		coltitles.add("");
		taskList.add(coltitles);	
		
		return taskList;
	}
	
	public static ErrorCodes delete(String taskID)
	{
		return SQLMethods.Delete("Tasks", "id = " + taskID) != -1? ErrorCodes.OK: ErrorCodes.databaseError;
	}

	public static ErrorCodes edit(String taskID, String title, String desc, String group, String hours,
								  String startDate, String dueDate)
	{	
		String[] columns = {"title", "description", "`group`", "hoursRequired", "startDate", "dueDate"};
		String [] newvalues = {"'" + title + "'","'" + desc + "'",
								group==null?"null":"'" + group + "'",
								hours, "'" + startDate + "'", "'" + dueDate + "'"};		
		
		return SQLMethods.Update("Tasks", columns, newvalues, "id = " + taskID) != -1? ErrorCodes.OK: ErrorCodes.databaseError;
	}
	
	
}
